
rm(list = ls())

## load libraries
library(tidyverse)
library(haven)

### ibes actuals ###############################################################
ibes_actq <- read_dta("01_InData/ibes_actu_epsus.dta") %>% 
  filter(pdicity  == "QTR",
         measure  == "EPS",
         curr_act == "USD") %>% 
  rename(fpedats          = pends,
         actual_q         = value,
         anntims_actual_q = anntims,
         anndats_actual_q = anndats) %>% 
  distinct(ticker, fpedats, .keep_all = TRUE) %>% 
  arrange(ticker, fpedats) %>%
  group_by(ticker) %>% 
  mutate(anndats_actual_q_lag_1 = lag(anndats_actual_q, 1L)) %>% 
  ungroup() %>% 
  filter(!is.na(anndats_actual_q_lag_1)) %>% 
  select(ticker, fpedats, actual_q, anntims_actual_q, anndats_actual_q,
         anndats_actual_q_lag_1)


### individual analyst forecasts ###############################################
analyst_quarterly <- read_dta("01_InData/ibes_detu_epsus.dta") %>% 
  filter(usfirm == 1,
         fpi == 6,
         analys > 1) %>% 
  arrange(analys, ticker, fpedats, anndats) %>%
  group_by(analys, ticker, fpedats, anndats) %>%
  mutate(max_anntims = max(anntims)) %>%
  ungroup() %>% 
  filter(anntims == max_anntims) %>% 
  group_by(analys, ticker, fpedats, anndats) %>%
  mutate(max_revtims = max(revtims)) %>%
  ungroup() %>% 
  filter(revtims == max_revtims) %>% 
  group_by(analys, ticker, fpedats, anndats) %>%
  mutate(max_acttims = max(acttims)) %>%
  ungroup() %>% 
  filter(acttims == max_acttims) %>% 
  group_by(analys, ticker, fpedats, anndats) %>%
  mutate(day_n = n()) %>%
  ungroup() %>% 
  filter(day_n == 1) %>% 
  inner_join(ibes_actq, by = c("ticker", "fpedats")) %>% 
  filter(anndats < anndats_actual_q,
         anndats > anndats_actual_q_lag_1) %>% 
  arrange(analys, ticker, fpedats, anndats) %>% 
  group_by(analys, ticker, fpedats) %>% 
  mutate(max_anndats   = max(anndats)) %>% 
  ungroup() %>% 
  filter(anndats   == max_anndats) %>% 
  rename(anndats_q = anndats,
         value_q   = value,
         TICKER    = ticker,
         PENDS     = fpedats,
         ANALYS    = analys) %>% 
  mutate(quart_eps_fcast_error = actual_q - value_q,
         PENDS_year  = lubridate::year(PENDS),
         PENDS_month = lubridate::month(PENDS),
         PENDS_day   = lubridate::day(PENDS)) %>% 
  select(TICKER, ANALYS, PENDS_year, PENDS_month, PENDS_day, quart_eps_fcast_error)


write_dta(analyst_quarterly, "02_OutData/quarterly_surprises.dta")

